/**
 * 
 */
package com.infodms.dms.dao.sales.storageManage;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import com.infodms.dms.common.Constant;
import com.infodms.dms.common.Utility;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.dao.sales.ordermanage.reqquery.ReqQueryDao;
import com.infodms.dms.util.CommonUtils;
import com.infodms.dms.util.businessUtil.GetVinUtil;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;

public class ReturnVehicleReqDao extends BaseDao<PO>{ 
	public static Logger logger = Logger.getLogger(ReturnVehicleReqDao.class);
	
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}
	
	public PageResult <Map<String,Object>> getReturnVehicleReq(String dealerId,String materialCode,String materialCode__ , String vin, int pageSize,int curPage){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT G.GROUP_NAME AS MODEL_NAME, --车型\n");
		sql.append("       G1.GROUP_NAME AS PACKAGE_NAME, --配置\n");  
		sql.append("       TMVM.MATERIAL_NAME, --物料\n"); 
		sql.append("       TMVM.MATERIAL_CODE,\n");
		sql.append("       TMV.VEHICLE_ID, TMV.VIN, TMV.DEALER_ID, --VIN\n");  
		sql.append("       TDW.WAREHOUSE_NAME, --位置说明\n");  
		sql.append("       TMD.DEALER_SHORTNAME, \n");
		sql.append("       TMV.LIFE_CYCLE, --库存状态\n");  
		sql.append("       (SELECT TCO.CODE_DESC FROM TC_CODE TCO WHERE TCO.CODE_ID=TMV.LIFE_CYCLE) AS LIFE_CYCLE_DESC, --库存状态描述\n"); 
		sql.append("       TO_CHAR(TMV.STORAGE_DATE, 'yyyy-MM-dd') AS STORAGE_TIME, --入库日期\n");  
		sql.append("       (CEIL (SYSDATE - TMV.STORAGE_DATE)) AS DAY_COUNT --库存天数\n");  
		sql.append("  FROM TM_VEHICLE             TMV,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP G,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP G1,\n");  
		sql.append("       TM_VHCL_MATERIAL       TMVM,\n");  
		sql.append("       TM_DEALER TMD, \n");
		sql.append("       TM_DEALER_WAREHOUSE TDW \n");
		sql.append(" WHERE TMV.LIFE_CYCLE = " + Constant.VEHICLE_LIFE_03 + " \n"); 
		sql.append("   AND TMV.LOCK_STATUS = "+ Constant.LOCK_STATUS_01 +"\n");  
		sql.append("   AND TMV.DEALER_ID IN ("+dealerId+")\n");  
		sql.append("   AND TMV.MODEL_ID = G.GROUP_ID\n");  
		sql.append("   AND TMV.PACKAGE_ID = G1.GROUP_ID\n");  
		sql.append("   AND TMV.MATERIAL_ID = TMVM.MATERIAL_ID\n");  
		sql.append("   AND TMV.DEALER_ID = TMD.DEALER_ID \n");
		sql.append("   AND TMV.VEHICLE_AREA = TDW.WAREHOUSE_ID(+) \n");
		if (null != materialCode && !"".equals(materialCode)) {
			String[] materialCodes = materialCode.split(",");
			if (null != materialCodes && materialCodes.length>0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'").append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length()-1);
				sql.append("   AND ((TMV.PACKAGE_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                               FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                              WHERE G.GROUP_CODE IN ("+buffer.toString()+"))) \n");
				sql.append("       OR (TMV.MODEL_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                             FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                            WHERE G.GROUP_CODE IN ("+buffer.toString()+"))) \n");
				sql.append("       OR (TMV.SERIES_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                              FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                             WHERE G.GROUP_CODE IN ("+buffer.toString()+"))) \n");
				sql.append("       OR  SUBSTR(G.TREE_CODE, 0, 3) IN\n");
				sql.append("       (SELECT G.TREE_CODE\n");  
				sql.append("           FROM TM_VHCL_MATERIAL_GROUP G\n");  
				sql.append("          WHERE G.GROUP_CODE IN ("+buffer.toString()+")))\n");

			}
			
		}
		if (null != materialCode__ && !"".equals(materialCode__)) {
			String[] array = materialCode__.split(",");
			sql.append("   AND TMVM.MATERIAL_CODE IN (\n");
			for (int i = 0; i < array.length; i++) {
				sql.append("'" + array[i] + "'");
				if (i != array.length - 1) {
					sql.append(",");
				}
			}
			sql.append(")\n");
		}
		if (null != vin && !"".equals(vin)) {
			sql.append(GetVinUtil.getVins(vin,"TMV")); 
		}
		sql.append("ORDER BY TMV.STORAGE_DATE DESC \n");
		
		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}
	
	public PageResult <Map<String,Object>> getQueryReturnVehicleReq(String orgId, String status, String dealerId,String materialCode,String materialCode__ , String vin, String areaIds, int pageSize,int curPage){
		StringBuffer sql = new StringBuffer("");
		
		sql.append("SELECT G.GROUP_NAME AS MODEL_NAME, --车型\n");
		sql.append("       G1.GROUP_NAME AS PACKAGE_NAME, --配置\n");  
		sql.append("       TMVM.MATERIAL_NAME, --物料\n"); 
		sql.append("       TMVM.MATERIAL_CODE,\n");
		sql.append("       TMV.VEHICLE_ID, TMV.VIN, A.DEALER_ID, --VIN\n");  
		sql.append("       TDW.WAREHOUSE_NAME, --位置说明\n");  
		sql.append("       TMD.DEALER_SHORTNAME, \n");
		sql.append("       TMD.DEALER_NAME, \n");
		sql.append("       A.REQ_NO, \n");
		sql.append("	   A.REASON,  A.CHECK_REMARK, A.STATUS REQ_STATUS,\n" );
		sql.append("	   (SELECT TCO.CODE_DESC FROM TC_CODE TCO WHERE TCO.CODE_ID=A.STATUS) AS REQ_STATUS_DESC,\n" );
		sql.append("	   TO_CHAR(A.CHECK_DATE, 'YYYY-MM-DD') CHECK_DATE,\n" );
		sql.append("	   TO_CHAR(A.RAISE_DATE, 'YYYY-MM-DD') RAISE_DATE,");
		sql.append("       TMV.LIFE_CYCLE, --库存状态\n");  
		sql.append("       TO_CHAR(TMV.STORAGE_DATE, 'yyyy-MM-dd') AS STORAGE_TIME, --入库日期\n");  
		sql.append("       (CEIL (SYSDATE - TMV.STORAGE_DATE)) AS DAY_COUNT --库存天数\n");  
		sql.append("  FROM TM_VEHICLE             TMV,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP G,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP G1,\n");  
		sql.append("       TM_VHCL_MATERIAL       TMVM,\n");  
		sql.append("       TM_DEALER TMD, \n");
		sql.append("       TM_DEALER_WAREHOUSE TDW, \n");
		sql.append("       TT_VS_RETURN_VEHICLE_REQ A \n");
		sql.append(" WHERE TMV.VEHICLE_ID = A.VEHICLE_ID \n"); 
		if(Utility.testString(dealerId)){
			sql.append("   AND A.DEALER_ID IN ("+dealerId+")\n");  
		}
		if(Utility.testString(status)){
			sql.append("   AND A.STATUS = ").append(status).append("\n");
		}
		sql.append("   AND TMV.MODEL_ID = G.GROUP_ID\n");  
		sql.append("   AND TMV.PACKAGE_ID = G1.GROUP_ID\n");  
		sql.append("   AND TMV.MATERIAL_ID = TMVM.MATERIAL_ID\n");  
		sql.append("   AND A.DEALER_ID = TMD.DEALER_ID \n");
		sql.append("   AND TMV.VEHICLE_AREA = TDW.WAREHOUSE_ID(+) \n");
		if (null != materialCode && !"".equals(materialCode)) {
			String[] materialCodes = materialCode.split(",");
			if (null != materialCodes && materialCodes.length>0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'").append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length()-1);
				sql.append("   AND ((TMV.PACKAGE_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                               FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                              WHERE G.GROUP_CODE IN ("+buffer.toString()+"))) \n");
				sql.append("       OR (TMV.MODEL_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                             FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                            WHERE G.GROUP_CODE IN ("+buffer.toString()+"))) \n");
				sql.append("       OR (TMV.SERIES_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                              FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                             WHERE G.GROUP_CODE IN ("+buffer.toString()+"))) \n");
				sql.append("       OR  SUBSTR(G.TREE_CODE, 0, 3) IN\n");
				sql.append("       (SELECT G.TREE_CODE\n");  
				sql.append("           FROM TM_VHCL_MATERIAL_GROUP G\n");  
				sql.append("          WHERE G.GROUP_CODE IN ("+buffer.toString()+")))\n");

			}
			
		}
		if (null != materialCode__ && !"".equals(materialCode__)) {
			String[] array = materialCode__.split(",");
			sql.append("   AND TMVM.MATERIAL_CODE IN (\n");
			for (int i = 0; i < array.length; i++) {
				sql.append("'" + array[i] + "'");
				if (i != array.length - 1) {
					sql.append(",");
				}
			}
			sql.append(")\n");
		}
		if (null != vin && !"".equals(vin)) {
			sql.append(GetVinUtil.getVins(vin,"TMV")); 
		}
		if(Utility.testString(areaIds)){
			sql.append("   AND TMV.AREA_ID IN ("+areaIds+")\n");  
		}
		
		if(!CommonUtils.isNullString(orgId)) {
			sql.append(ReqQueryDao.limitOrg("TMD", "dealer_id", orgId)) ;
		}
		
		sql.append("ORDER BY TMV.STORAGE_DATE DESC \n");
		
		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}
	
	public Long getAreaId(Long id){
		Long areaId = null;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT AREA_ID FROM TM_DEALER_BUSINESS_AREA WHERE DEALER_ID = ").append(id);
		Map<String, Object> map = pageQueryMap(sql.toString(), null, getFunName());
		if(map!=null){
			areaId = Long.parseLong(String.valueOf(map.get("AREA_ID")));
		}
		return areaId;
	}
	
	public Long getVAreaId(String id){
		List<Object> params = new ArrayList<Object>() ;
		Long areaId = null;
		
		StringBuffer sql = new StringBuffer("\n");
		sql.append("select tmv.area_id from tm_vehicle tmv where tmv.vehicle_id = ?\n");
		params.add(id) ;
		
		Map<String, Object> map = pageQueryMap(sql.toString(), params, getFunName());
		
		if(map!=null){
			areaId = Long.parseLong(String.valueOf(map.get("AREA_ID")));
		}
		
		return areaId;
	}
	
	public PageResult <Map<String,Object>> getApplyReturnVehicleReq(String orgId, String dealerId,String areaId, String materialCode,String materialCode__ , String vin, int pageSize,int curPage){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT A.REQ_ID, G.GROUP_NAME AS MODEL_NAME, --车型\n");
		sql.append("       G1.GROUP_NAME AS PACKAGE_NAME, --配置\n");  
		sql.append("       TMVM.MATERIAL_NAME, --物料\n"); 
		sql.append("       TMVM.MATERIAL_CODE,\n");
		sql.append("       TMV.VEHICLE_ID, TMV.VIN, TMV.DEALER_ID, --VIN\n");  
		sql.append("       TDW.WAREHOUSE_NAME, --位置说明\n");  
		sql.append("       TMD.DEALER_SHORTNAME, \n");
		sql.append("       TMD.DEALER_NAME, \n");
		sql.append("	   A.REQ_NO,\n") ;
		sql.append("	   A.REASON,  A.CHECK_REMARK, A.STATUS REQ_STATUS,\n" );
		sql.append("	   TO_CHAR(A.CHECK_DATE, 'YYYY-MM-DD') CHECK_DATE,\n" );
		sql.append("	   TO_CHAR(A.RAISE_DATE, 'YYYY-MM-DD') RAISE_DATE,");
		sql.append("       TMV.LIFE_CYCLE, --库存状态\n");  
		sql.append("       TO_CHAR(TMV.STORAGE_DATE, 'yyyy-MM-dd') AS STORAGE_TIME, --入库日期\n");  
		sql.append("       (CEIL (SYSDATE - TMV.STORAGE_DATE)) AS DAY_COUNT --库存天数\n");  
		sql.append("  FROM TM_VEHICLE             TMV,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP G,\n");  
		sql.append("       TM_VHCL_MATERIAL_GROUP G1,\n");  
		sql.append("       TM_VHCL_MATERIAL       TMVM,\n");  
		sql.append("       TM_DEALER TMD, \n");
		sql.append("       TM_DEALER_WAREHOUSE TDW, \n");
		sql.append("       TT_VS_RETURN_VEHICLE_REQ A \n");
		sql.append(" WHERE TMV.VEHICLE_ID = A.VEHICLE_ID \n"); 
		sql.append(" AND A.STATUS = ").append(Constant.RETURN_STATUS_01).append("\n"); 
		if(Utility.testString(dealerId)){
			sql.append("   AND TMV.DEALER_ID IN ("+dealerId+")\n");  
		}
		if(Utility.testString(areaId)){
			sql.append("   AND A.AREAID = ").append(areaId).append("\n");
		}
		sql.append("   AND TMV.MODEL_ID = G.GROUP_ID\n");  
		sql.append("   AND TMV.PACKAGE_ID = G1.GROUP_ID\n");  
		sql.append("   AND TMV.MATERIAL_ID = TMVM.MATERIAL_ID\n");  
		sql.append("   AND TMV.DEALER_ID = TMD.DEALER_ID \n");
		sql.append("   AND TMV.VEHICLE_AREA = TDW.WAREHOUSE_ID(+) \n");
		if (null != materialCode && !"".equals(materialCode)) {
			String[] materialCodes = materialCode.split(",");
			if (null != materialCodes && materialCodes.length>0) {
				StringBuffer buffer = new StringBuffer();
				for (int i = 0; i < materialCodes.length; i++) {
					buffer.append("'").append(materialCodes[i]).append("'").append(",");
				}
				buffer = buffer.deleteCharAt(buffer.length()-1);
				sql.append("   AND ((TMV.PACKAGE_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                               FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                              WHERE G.GROUP_CODE IN ("+buffer.toString()+"))) \n");
				sql.append("       OR (TMV.MODEL_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                             FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                            WHERE G.GROUP_CODE IN ("+buffer.toString()+"))) \n");
				sql.append("       OR (TMV.SERIES_ID IN (SELECT G.GROUP_ID \n");
				sql.append("                              FROM TM_VHCL_MATERIAL_GROUP G \n");
				sql.append("                             WHERE G.GROUP_CODE IN ("+buffer.toString()+"))) \n");
				sql.append("       OR  SUBSTR(G.TREE_CODE, 0, 3) IN\n");
				sql.append("       (SELECT G.TREE_CODE\n");  
				sql.append("           FROM TM_VHCL_MATERIAL_GROUP G\n");  
				sql.append("          WHERE G.GROUP_CODE IN ("+buffer.toString()+")))\n");

			}
			
		}
		if (null != materialCode__ && !"".equals(materialCode__)) {
			String[] array = materialCode__.split(",");
			sql.append("   AND TMVM.MATERIAL_CODE IN (\n");
			for (int i = 0; i < array.length; i++) {
				sql.append("'" + array[i] + "'");
				if (i != array.length - 1) {
					sql.append(",");
				}
			}
			sql.append(")\n");
		}
		if (null != vin && !"".equals(vin)) {
			sql.append(GetVinUtil.getVins(vin,"TMV")); 
		}
		
		if(!CommonUtils.isNullString(orgId)) {
			sql.append(ReqQueryDao.limitOrg("TMD", "dealer_id", orgId)) ;
		}
		
		sql.append("ORDER BY TMV.STORAGE_DATE DESC \n");
		
		PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
		return ps;
	}
	
}
